package jdbc418;

import com.mysql.jdbc.Driver;

import javax.lang.model.SourceVersion;
import java.sql.*;

/**
 * jdbc  操作数据库的一套接口  规范  具体的实现由各大数据库厂商完成
 * 使用JDBC连接数据库  java -> mysql  -> sql 语句
 * 步骤：
 *    1.加载驱动（实现类）
 *    2.获取数据库连接
 *    3.获取操作数据库的对象
 *    4.编写sql语句，并通过操作数据库的对象发送sql
 *    5.处理结果集 （select） update insert delete -> 影响的行数 line
 *    6.关闭连接 释放资源
 *
 *
 */
public class jdbcDemo01 {
    //快速生成主方法 psvm main
    public static void main(String[] args) {
        test07();

    }
    public static void test01(){//获取数据库连接的方式1
        try {
            //加载驱动
            Driver driver = new Driver();
            //注册驱动
            DriverManager.registerDriver(driver);

            /**
             * 获取连接  getConnection(String url,string user,String password)
             * http://www.baidu.com
             * 参数1：数据库连接的地址
             *       统一资源定位符  jdbc:mysql://loclhost:3306/数据库名
             *       jdbc:mysql：通讯的协议
             *       loclhost:3306：数据库的地址和端口号
             * 参数2：数据库的用户名 root
             * 参数3：数据库的密码 root
             */
            //如果报错根据具体的类型进行修改
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            Connection connection = DriverManager.getConnection(url, user, password);
            System.out.println("connection = " + connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void test02(){//获取数据库连接的方式2  推荐

        try {
            //通过反射完成驱动的注册
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            Connection connection = DriverManager.getConnection(url, user, password);
            System.out.println("connection = " + connection);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public static void test03(){//获取操作数据库的对象 操作数据库 crud 增删改查
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的查询操作
            String sql = "select * from dept";
            //发送sql 接受结果集
            resultSet = statement.executeQuery(sql);
            //处理结果集  resultSet.next() -> boolean true ->处理结果
            while(resultSet.next()){//获取当前记录的每个字段
                //根据当前字段的数据类型以及字段名获取字段值
                int dept_no = resultSet.getInt("dept_no");
                //通过列的索引和数据类型获取字段值
                String dept_name = resultSet.getString(2);
                String address = resultSet.getString(3);

                System.out.println("dept_no = " + dept_no);
                System.out.println("dept_name = " + dept_name);
                System.out.println("address = " + address);


                System.out.println("---------------------------" );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {//释放资源

            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

    }

    //test04与上面的03进行比较
    public static void test04(){//获取操作数据库的对象 操作数据库 crud 增删改查
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的查询操作
            String sql = "select * from dept";
            //发送sql 接受结果集
            resultSet = statement.executeQuery(sql);
            //处理结果集  resultSet.next() -> boolean true ->处理结果
            while(resultSet.next()){//获取当前记录的每个字段
                //根据当前字段的数据类型以及字段名获取字段值
                int dept_no = resultSet.getInt("dept_no");
                //通过列的索引和数据类型获取字段值
                String dept_name = resultSet.getString(2);
                String address = resultSet.getString(3);

               /* System.out.println("dept_no = " + dept_no);
                System.out.println("dept_name = " + dept_name);
                System.out.println("address = " + address);*/

                dept dept1 = new dept(dept_no, dept_name, address);
                System.out.println("dept1" +dept1);

                System.out.println("---------------------------" );
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {//释放资源

            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

    }
    public static void test05(){//使用jdbc插入数据到数据库
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的插入操作
            String sql = "insert into dept(dept_no,dept_name,address)values(50,'监察部','北京')";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"插入成功":"插入失败");


        } catch (Exception e) {
            e.printStackTrace();
        }finally {//释放资源

            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

    }
    public static void test06(){//使用jdbc删除数据库中一条记录
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的插入操作
            String sql = "delete from dept where dept_no = 50 ";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"删除成功":"删除失败");


        } catch (Exception e) {
            e.printStackTrace();
        }finally {//释放资源

            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

    }
    public static void test07(){//使用jdbc更新数据到数据库
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);

            //获取操作数据库的对象
            statement = connection.createStatement();
            //数据库的插入操作
            String sql = "update  dept set address = '上海' where dept_no = 40";
            //返回值是影响的行数
            int i = statement.executeUpdate(sql);
            System.out.println(i>0?"更新成功":"更新失败");


        } catch (Exception e) {
            e.printStackTrace();
        }finally {//释放资源

            if (statement!=null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

    }

    public static void test08(){//SQL注入
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/my?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
            String user = "root";
            String password = "root";
            //获取连接
            Connection connection = DriverManager.getConnection(url, user, password);
            Statement statement = connection.createStatement();
            //编写sql
            String sql = "select * from dept where ";
            //打印sql语句  sql注入
            ResultSet resultSet = statement.executeQuery(sql);
            dept dept01 = null;
            while(resultSet.next()){
                int dept_no = resultSet.getInt("dept_no");




            }

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
